New Star Software Collection

home *** CD-ROM | disk | FTP | other *** search

/ New Star Software Collection / NSS_Collection.iso / 3-170 dbase 10 for windows / 1.ima / DOC.PAK / QUERY.TXT < prev next >

Wrap

Text File | 1993-07-26 | 14.4 KB | 371 lines

Copyright (c) 1991-1993 Borland International, Inc. All Rights Reserved. INTRODUCTION TO THE QUERY DESIGNER ---------------------------------- This paper describes the current status of the Bladerunner Query Designer, including features not yet implemented. Contents -------- I. Design Goals II. How to Start the Query Designer III. Basic Query Layout IV. Fields List V. Table Relations VI. Sort Criteria VII. Filter Conditions VIII. Features Not Implemented Yet Design Goals ------------ The Blade Runner Query Designer pursues these objectives: Similarity to dBASE IV -- It is a more modern, graphical version of the dBASE IV Query Designer. It retains as much functionality as possible from dBASE IV. Where practical, it uses the same concepts, keystrokes, and terminology as dBASE IV. However, this goal does not bar innovative new features or adherence to normal Windows behavior. Suitable for Views and Queries -- Like dBASE IV, it is suitable for view definition and query specification. For this reason, it creates updatable views whenever possible. Generate dBASE Program Files -- Like dBASE IV, the query designer's output is a .QBE file containing a dBASE program. The resulting code can readily work in a customer's application. Multiple Simultaneous Queries -- Unlike dBASE IV, the Query Designer is non-modal. A user can design multiple queries simultaneously. How to Start the Query Designer ------------------------------- You can start the Query Designer in one of the following ways: - From the menu bar, choose File|New|Query - In the Command window, enter: CREATE QUERY or MODIFY QUERY - In the File Viewer, click on the Queries icon, then the Untitled query icon. Basic Query Layout ------------------ Like dBASE IV, the Query Designer is a separate MDI window containing a table UI object for each table involved in the query. The table UI object is a schematic representation of a database table and lists the field names contained in the table. Most of the interaction with the Query Designer is by direct manipulation of the table objects. There is also a menu and SpeedBar that contain actions specific to the Query Designer. The Query Designer initially starts out empty (with no tables) unless an existing query is being modified. Tables are added to a query by the Query | Add Table menu or SpeedBar button. New tables are added below any existing tables in the designer window. Tables can be removed from a query by choosing Query | Remove Table or clicking the first button from the right on the SpeedBar. If there are multiple tables displayed in the Query Designer, one of them is the currently selected table. Menu and SpeedBar actions that apply to a single table operate on the currently selected table. The selected table is identified by a blinking cursor in one of its fields. The currently selected table can be changed by the F3 (previous) and F4 (next) keys or by clicking on the table with the mouse. If the Query window contains more tables than can be displayed vertically, a vertical scroll bar appears automatically. If a table UI object is wider than the query window, it can be scrolled horizontally using tab keys and/or clicking the scroll arrows just to the right of the table name. The designer automatically scrolls table UI objects and fields into view as necessary. Fields within a table UI object all start out at the same default width (unrelated to the size of the field data). The size of any field column can be modified by dragging the column divider between field names in the top row of the table UI object. As filter conditions are added into the column, the column width automatically expands to display the entire filter condition. The order of the field columns can be modified by dragging a field name horizontally to a new position (similar to BROWSE). There are three possible outputs from the Query Designer that are available at any time: 1) Save .QBE File. Choose File|Save to create a dBASE program file that sets up the dBASE environment to correspond with the query when executed. You must give the file an extension. 2) Run Query. Choose View|Run Query to convert the Query window into a Browse window within an environment that corresponds to the query. (Note: Currently, the browse appears in a separate window.) 3) Create Result Table. This action creates a table that contains the results from evaluating the query. Fields List ----------- Fields are selected into a query in a manner very similar to dBASE IV. Individual fields can be selected (or unselected) by tabbing to the field and pressing F5 or by checking (or unchecking) the checkbox in the row below the field names. All of the fields in a table can be selected (or unselected) by tabbing to the table name column and pressing F5 or by checking (or unchecking) the checkbox below the table name. Field selection is not echoed in the Command window on a field by field basis. An appropriate SET FIELDS command is either generated into the .QBE file or executed immediately before switching to the browse. This is required to support multiple simultaneous queries. If no fields are selected into a query, then all fields are assumed to be included in the view (by SET FIELDS TO). Table Relations --------------- The Blade Runner Query Designer has an explicit interface for creating table relationships (it doesn't use example elements). This is a more natural way to specify and display relationships between tables. A relationship is defined by dragging the mouse between the parent table name and the child table name. The cursor changes shape during this operation to give visual feedback that a relationship is being defined. Once the drag has been completed, a dialog box appears to gather information necessary to relate the tables. The Define Link dialog has two ways to specify the master expression for the relation. A field name can be selected from a listbox to create a relation based on a single field value. Alternatively, for dBASE tables only, a master expression can be entered in an entry field to control the relation. The Expression Expert is available at this time to help the user construct a valid dBASE expression. (Note: This second option has not been fully implemented.) The Define Link dialog also provides a means to specify the index to be used for the child table. An index expression can be selected from a listbox to select the controlling index for the child table. This list box contains all possible index expressions for the child table and includes indexes from associated .NDX and .MDX files. This selected index order is immediately used by echoing an appropriate SET ORDER command to the Command window. When the Define Link dialog appears while constructing a relation, it is initialized with default values, if possible. A default value is detected when a child index expression has the same name and data type as a field in the parent table. (Note: This feature has not been implemented.) Once a relationship has been defined, the Query Designer indents the child table under the parent table and draws an arrow between them. Tables are automatically rearranged within the query window, if necessary, to move the child table as close as possible to the parent table (so lines don't cross over other tables). A previously defined relationship can be inspected or modified by selecting the child table and using the Properties | Relation menu command or by right-clicking the child table name. The same Define Link dialog box is used for this purpose as for the initial creation. Sort Criteria ------------- The sort order for each table is controlled using multiple choice checkboxes preceding the field names in the table UI objects. Each field in a table can individually be set to one of three states: 1) off, 2) ascending, or 3) descending. Like dBASE IV, a field may be involved in determining sort order without being selected into the result set (fields list). When a table is added to a query, it defaults to natural order with all of the sort checkboxes set to "off." The sort checkbox for any field can be toggled by selecting the field and pressing F6 or by selecting a choice from a pop-up menu that appears with a left mouse click. (Note: Currently, child tables in a relation do not permit modification of the sort order.) For dBASE and Paradox tables, there may be maintained indexes which are based on a single field. Such fields have a key symbol next to the sort checkbox in the table UI object to indicate that the table may be sorted most efficiently using one of these fields. The resulting dBASE code to implement a sort on a table depends on the number and type of sort fields specified by the user. There are three basic situations: 1) Natural Order or Existing Index. When no sort order is specified or when a maintained index can be used, the table is opened with the appropriate ORDER clause. The following code is generated when no sort fields are specified or when an existing index expression can be used: CLOSE DATABASES SET EXACT ON SELECT 1 USE CONTACT.DBF ORDER COMPCODE GO TOP 2) Single Field. When a single field is specified to determine sort order for which an index expression does not exist, a temporary index is created. The temporary index is created in an .MDX file in the current directory with the name ~QBEn, where n is the work area number. The temporary index file is automatically deleted when the table is closed using the NOSAVE option. The following code is generated when a single sort field is specified: CLOSE DATABASES SET EXACT ON SELECT 1 USE COMPANY.DBF EXCLUSIVE INDEX ON CITY TAG CITY OF ~QBE0001 CLOSE DATABASES USE COMPANY.DBF SET ORDER TO CITY OF ~QBE0001 NOSAVE GO TOP 3) Multiple Fields. When more than one field is specified to determine sort order, a sorted intermediate table is created. The order of the fields in the table UI object determine their precedence in the sort. The relative precedence of fields can be controlled by rearranging the fields within the table UI object. The sorted table is created in the current directory with the name ~SORTn, where n is the work area number. The intermediate table is automatically deleted when the table is closed using the NOSAVE option. The following code is generated when multiple sort fields are specified: CLOSE DATABASES SET EXACT ON SELECT 1 USE COMPANY.DBF SET FILTER TO GO TOP SORT TO ~SORT001 ON CITY,ZIP USE ~SORT001 ALIAS COMPANY NOSAVE NOUPDATE GO TOP Filter Conditions ----------------- Like dBASE IV, query filter conditions are entered in the rows beneath the field names in the table UI object. Each expression should evaluate to a logical and be of the form: <comparison operator> <value expression> which implies: (current field) <comparison operator> <value expression> If the operator is omitted, an equal operator is assumed. The value expression should be of the same type as the current field. Like dBASE IV, string constants must be enclosed in quote marks. More than one filter condition can be entered into a single field separated by commas. All filter conditions entered on the same row are combined by logical AND operators. Additional condition rows may be added by the down arrow key. (Empty rows on the bottom are automatically removed by the up arrow key.) If there are conditions in more than one row, these are combined by logical OR operators. Filter expressions are checked for errors at the time they are entered (when the user attempts to leave the field). Invalid expressions are highlighted for the user to fix and are not included in the generated .QBE file output. (Note: Error messages will show in the status bar when an error is detected.) Like dBASE IV, conditions for related tables are included in the filter expression of the parent table. In these situations, the parent filter expression also contains a FOUND function to insure that a child record exists (inner join). The following code is generated when a condition exists in both a parent and child table: CLOSE DATABASES SET EXACT ON SELECT 1 USE COMPANY.DBF USE CONTACT.DBF IN 2 ORDER COMPCODE SET RELATION TO COMPCODE INTO CONTACT SET FILTER TO FOUND(2) .AND. COMPANY>"C" .AND. CONTACT->DUMMY>"N" GO TOP Unlike dBASE IV, Blade Runner always filters database records using SET FILTER logic. (dBASE IV also supports the use of SET KEY and FOR..WHILE, as well.) Blade Runner uses SET FILTER to insure that queries work well in a multiuser environment and to allow filter optimization to occur within the ODAPI database engine. Features Not Implemented Yet ---------------------------- The following features will be supported in Bladerunner, but are not operative at this time: Calculated Fields -- An interface will be provided to allow specification of a calculated field derived from other data. Calculated fields will be placed into an additional table UI object. The form of this specification is a dBASE expression which is included in the fields list. Renamed Fields -- An interface will be provided to allow specification of a different name for a field. The result is very similar to a calculated field included in the fields list. Condition Box -- A condition box, similar to dBASE IV, will be provided to specify additional conditions that cannot be specified within a single field condition (i.e. FIELD1>FIELD2). Sorting Child Tables -- The appropriate logic to permit sorting within related tables will be determined and implemented. Design versus Run Mode -- The UI model for Bladerunner calls for a mode change between design and run to occur within the same window. Currently, a separate Browse window is created rather than reusing the query design window. Non-modal Behavior -- Unlike dBASE IV, the Bladerunner Query Designer is non-modal. This creates situations where the user can make changes to tables involved in a query from outside of the Query Designer (i.e. close a table from the Command window). These situations must be adequately detected and resolved or else be prevented from occurring.